package com.cy;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileOutputStream;

public class ExcelWriteTest {

	String PATH = "F:\\work\\CODES\\easyexcel\\pio/";

	@Test
	public void testWrite03() throws Exception {
		//1.创建工作簿
		Workbook workbook = new HSSFWorkbook();
		//2.创建工作表
		Sheet sheet = workbook.createSheet("观众统计表");
		//3.创建一行
		Row row1 = sheet.createRow(0);
		//4.创建一个单元格
		Cell cell11 = row1.createCell(0);
		cell11.setCellValue("今日新增观众");
		//(1,2)
		Cell cell12 = row1.createCell(1);
		cell12.setCellValue(666);

		//第二行(2,1)
		Row row2 = sheet.createRow(1);
		Cell cell21 = row2.createCell(0);
		cell21.setCellValue("统计时间");
		//(2,2)
		Cell cell22 = row2.createCell(1);
		String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
		cell22.setCellValue(time);

		//生成一张表(IO流) 03版本使用xls结尾
		FileOutputStream fileOutputStream = new FileOutputStream(PATH + "观众统计表03.xls");
		//输出
		workbook.write(fileOutputStream);
		//关闭流
		fileOutputStream.close();

		System.out.println("观众统计表03,生成完毕");
	}

	@Test
	public void testWrite07() throws Exception {
		//1.创建工作簿 07
		Workbook workbook = new XSSFWorkbook();
		//2.创建工作表
		Sheet sheet = workbook.createSheet("观众统计表");
		//3.创建一行
		Row row1 = sheet.createRow(0);
		//4.创建一个单元格
		Cell cell11 = row1.createCell(0);
		cell11.setCellValue("今日新增观众");
		//(1,2)
		Cell cell12 = row1.createCell(1);
		cell12.setCellValue("666");

		//第二行(2,1)
		Row row2 = sheet.createRow(1);
		Cell cell21 = row2.createCell(0);
		cell21.setCellValue("统计时间");
		//(2,2)
		Cell cell22 = row2.createCell(1);
		String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
		cell22.setCellValue(time);

		//生成一张表(IO流) 03版本使用xls结尾
		FileOutputStream fileOutputStream = new FileOutputStream(PATH + "观众统计表07.xlsx");
		//输出
		workbook.write(fileOutputStream);
		//关闭流
		fileOutputStream.close();

		System.out.println("观众统计表07,生成完毕");
	}

	//HSSFWorkbook
	@Test
	public void testWrite03BigData() throws Exception {
		//时间
		long start = System.currentTimeMillis();

		//创建一个工作簿
		Workbook workbook = new HSSFWorkbook();
		//创建表
		Sheet sheet = workbook.createSheet();
		//写入数据
		for (int rowNum = 0; rowNum < 65536; rowNum++) {
				//创建行
			Row row = sheet.createRow(rowNum);
			for (int cellNum = 0; cellNum < 10; cellNum++) {
				//创建列
				Cell cell = row.createCell(cellNum);
				cell.setCellValue(cellNum);
			}
		}
		System.out.println("完成");
		//输出
		FileOutputStream outputStream = new FileOutputStream(PATH + "testWrite03BigData.xls");
		workbook.write(outputStream);
		//关闭流
		outputStream.close();

		long end = System.currentTimeMillis();
		//计算时间
		System.out.println((double) (start-end)/1000);
	}

	//耗时长 优化:缓存  XSSFWorkbook
	@Test
	public void testWrite07BigData() throws Exception {
		//时间
		long start = System.currentTimeMillis();

		//创建一个工作簿
		Workbook workbook = new XSSFWorkbook();
		//创建表
		Sheet sheet = workbook.createSheet();
		//写入数据
		for (int rowNum = 0; rowNum < 100000; rowNum++) {
			//创建行
			Row row = sheet.createRow(rowNum);
			for (int cellNum = 0; cellNum < 10; cellNum++) {
				//创建列
				Cell cell = row.createCell(cellNum);
				cell.setCellValue(cellNum);
			}
		}
		System.out.println("完成");
		//输出
		FileOutputStream outputStream = new FileOutputStream(PATH + "testWrite07BigData.xlsx");
		workbook.write(outputStream);
		//关闭流
		outputStream.close();

		long end = System.currentTimeMillis();
		//计算时间
		System.out.println((double) (start-end)/1000);
	}

	//SXSSFWorkbook
	@Test
	public void testWrite07BigDataS() throws Exception {
		//时间
		long start = System.currentTimeMillis();

		//创建一个工作簿
//		Workbook workbook = new SXSSFWorkbook();
		Workbook workbook = new SXSSFWorkbook();
		//创建表
		Sheet sheet = workbook.createSheet();
		//写入数据
		for (int rowNum = 0; rowNum < 100000; rowNum++) {
			//创建行
			Row row = sheet.createRow(rowNum);
			for (int cellNum = 0; cellNum < 10; cellNum++) {
				//创建列
				Cell cell = row.createCell(cellNum);
				cell.setCellValue(cellNum);
			}
		}
		System.out.println("完成");
		//输出
		FileOutputStream outputStream = new FileOutputStream(PATH + "testWrite07BigDataS.xlsx");
		workbook.write(outputStream);
		//关闭流
		outputStream.close();
		//清除临时文件 会占用一些内存
		((SXSSFWorkbook)workbook).dispose();

		long end = System.currentTimeMillis();
		//计算时间
		System.out.println((double) (end-start)/1000);
	}




}
